
/*********************
 **  CreateTime: 2006-7-25 
 **********************/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using EntLibEC.Cmn;
using EntLibEC.Info.PIM;
using EntLibEC.Dac;

namespace EntLibEC.Dac.PIM
{
    /// <summary>
    /// Contains the the data access methods for PropertyDA.
    /// </summary>
    public abstract class PropertyDA
    {
        #region Sql Script
        private const string SQL_GET_ALL_PROPERTY
            = @"SELECT a.SysNo, PropertyDescription, IsActive, a.LastEditUserSysNo, a.LastEditTime, b.UserName AS LastEditUsername
				FROM PIM_Property a (NOLOCK) 
                LEFT JOIN Ent_Sys_User b (NOLOCK) ON a.LastEditUserSysNo = b.SysNo
                WHERE IsActive = 1
                ORDER BY PropertyDescription";

        public const string SQL_GET_ALL_PROPERTY_FOR_CACHE
            = @"SELECT SysNo, PropertyDescription 
                FROM dbo.PIM_Property 
                WHERE IsActive = 1 
                ORDER BY PropertyDescription";

        private const string SQL_GET_PROPERTY
            = @"SELECT a.SysNo, PropertyDescription, IsActive, a.LastEditUserSysNo, a.LastEditTime, b.UserName AS LastEditUsername
				FROM PIM_Property a (NOLOCK)
                LEFT JOIN Ent_Sys_User b (NOLOCK) ON a.LastEditUserSysNo = b.SysNo
                WHERE a.SysNo = @SysNo";


        private const string SQL_INSERT_PROPERTY = @"INSERT INTO PIM_Property(PropertyDescription,IsActive,LastEditUserSysNo,LastEditTime) VALUES (@PropertyDescription,@IsActive,@LastEditUserSysNo,GETDATE()) SELECT @@IDENTITY";
        private const string SQL_UPDATE_PROPERTY = @"UPDATE PIM_Property SET PropertyDescription=@PropertyDescription,IsActive=@IsActive,LastEditUserSysNo=@LastEditUserSysNo,LastEditTime=getdate() WHERE SysNo=@SysNo";
        private const string SQL_DELETE_PROPERTY = @"DELETE FROM PIM_Property WHERE SysNo = @SysNo";
        private const string SQL_CHECK_Description = @"SELECT SysNo FROM PIM_Property (NOLOCK) WHERE PropertyDescription=@PropertyDescription AND SysNo<>@SysNo";
        #endregion

        public static PropertyInfo Map(DataRow row)
        {
            PropertyInfo info = new PropertyInfo();

            info.SysNo = Convert.ToInt32(row["SysNo"]);
            info.PropertyDescription = row["PropertyDescription"].ToString();
            info.IsActive = Convert.ToInt32(row["IsActive"]) == 1;
            info.LastEditUserSysNo = Convert.ToInt32(row["LastEditUserSysNo"]);
            info.LastEditTime = Convert.ToDateTime(row["LastEditTime"]);

            info.LastEditUsername = row["LastEditUsername"].ToString();

            return info;
        }

        public static DataTable GetAllProperties()
        {
            return DataAccessFacade.GetDataTable(SQL_GET_ALL_PROPERTY);
        }

        public static PropertyInfo GetProperty(int sysNo)
        {
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@SysNo", SqlDbType.Int)
        };

            
            parms[0].Value = sysNo;

            DataTable dt = DataAccessFacade.GetDataTable(SQL_GET_PROPERTY, parms);
            if (dt == null || dt.Rows.Count != 1)
                return null;

            return Map(dt.Rows[0]);
        }

        public static int InsertProperty(PropertyInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@PropertyDescription", SqlDbType.NVarChar, 50),
                new SqlParameter("@IsActive", SqlDbType.Int),
                new SqlParameter("@LastEditUserSysNo", SqlDbType.Int)
            };
          
            parms[0].Value = info.PropertyDescription;
            parms[1].Value = info.IsActive;
            parms[2].Value = info.LastEditUserSysNo;

            object obj = DataAccessFacade.ExecScalarSqlCommand(SQL_INSERT_PROPERTY, parms);
            return Convert.ToInt32(obj);
        }

        public static int UpdateProperty(PropertyInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@SysNo", SqlDbType.Int),
                new SqlParameter("@PropertyDescription", SqlDbType.NVarChar, 50),
                new SqlParameter("@IsActive", SqlDbType.Int),
                new SqlParameter("@LastEditUserSysNo", SqlDbType.Int)
            };

            parms[0].Value = info.SysNo;
            parms[1].Value = info.PropertyDescription;
            parms[2].Value = info.IsActive;
            parms[3].Value = info.LastEditUserSysNo;

            object obj = DataAccessFacade.ExecSqlCommand(SQL_UPDATE_PROPERTY, parms);
            return Convert.ToInt32(obj);
        }

        public static void DeleteProperty(int SysNo)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@SysNo", SqlDbType.Int)
            
             };
            parms[0].Value = SysNo;
            DataAccessFacade.ExecSqlCommand(SQL_DELETE_PROPERTY, parms);
        }
        public static bool PropertyExist(PropertyInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@SysNo", SqlDbType.Int),
                new SqlParameter("@PropertyDescription", SqlDbType.NVarChar, 50)
               };
            parms[0].Value = info.SysNo;
            parms[1].Value = info.PropertyDescription;

            object obj = DataAccessFacade.ExecScalarSqlCommand(SQL_CHECK_Description, parms);
            if (obj == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        
        public static DataTable SearchProperty(PropertySearchCondition condition)
        {
            StringBuilder sqlStr = new StringBuilder(@"SELECT a.SysNo, PropertyDescription, IsActive, a.LastEditUserSysNo, a.LastEditTime, b.UserName AS LastEditUsername FROM PIM_Property a (NOLOCK) LEFT JOIN Ent_Sys_User b (NOLOCK) ON a.LastEditUserSysNo = b.SysNo WHERE 1=1");
            if (condition.IsPart == false)
            {
                if (condition.Description != string.Empty)
                {
                    sqlStr.Append(" AND PropertyDescription Like " + EntLibEC.Cmn.Util.ToSqlLikeString(condition.Description));
                }
            }
            if (condition.IsPart ==true)
            {
                if (condition.Description != string.Empty)
                {
                    sqlStr.Append(" AND PropertyDescription Like " + EntLibEC.Cmn.Util.ToSqlLikeStringR(condition.Description));
                }
            }

            if (condition.EditorUserId != 0)
            {
                sqlStr.Append(" AND a.LastEditUserSysNo='" + condition.EditorUserId + "'");
            }
            sqlStr.Append(" AND IsActive='" + (Convert.ToBoolean(condition.IsActive) ? 1 : 0) + "'");
            sqlStr.Append(" ORDER BY PropertyDescription");

            return DataAccessFacade.GetDataTable(sqlStr.ToString());
        }
    }
}
